package cn.lxycx.dataterrace.controller;


import cn.lxycx.dataterrace.interceptor.LoginInterceptor;
import com.jfinal.json.Json;
import cn.lxycx.dataterrace.util.QuickDD;
import cn.lxycx.dataterrace.util.ServletPro;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.log4j.Logger;

import com.jfinal.aop.Clear;
import com.jfinal.core.ActionKey;
import com.jfinal.core.Controller;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class BackController extends Controller{
	private Logger log = Logger.getLogger(BackController.class);
	private final String login_user = PropKit.get("login_user","username");
	private final String login_pass = PropKit.get("login_pass","password");
	private String driver = PropKit.get("driver");

	@Clear(LoginInterceptor.class)
	@ActionKey("/login_new")
	public void login_new(){
		renderJsp("login_new.jsp");
	}
	
	@Clear(LoginInterceptor.class)
	@ActionKey("/login")
	public void login(){
		renderJsp("login.jsp");
	}

	@ActionKey("/newbusListPlus")
	public void newbusListPlus(){
		Integer id = this.getParaToInt("id");
		this.setAttr("sqlid", id);
		renderJsp("newbusListPlus.jsp");
	}

	@ActionKey("/main")
	public void main(){
		Record user = this.getSessionAttr("user");
		List<Record> grouplist = Db.find("select s.id,s.name,c.classname from data_statement s left join data_statement_class c on s.classid=c.id   where s.status=1 and c.status=1 and s.id in(select sid from data_statement_limit where aid = ? ) order by c.id,s.id ",user.get("id"));
		Map<String,List<Record>> groupMap = new LinkedHashMap<String,List<Record>>();
		for(Record r:grouplist) {
			String classname = r.getStr("classname");
			List<Record> list = groupMap.get(classname);
			if(list==null){
				list = new ArrayList<Record>();
				groupMap.put(classname, list);
			}
			list.add(r);
		}
		this.setAttr("res", 1);
		this.setAttr("groupMap", groupMap);
		renderJsp("main.jsp");
	}
	
	@ActionKey("/welcome")
	public void welcome(){
		renderJsp("welcome.jsp");
	}
	
	public void outWord() throws Exception{
		String tablename = this.getPara("tablename");
		String sql = QuickDD.toLike(tablename);//toIn("JS_BABY_REGISTER");
		System.out.println(sql);
		String name = "公安认证数据结构文档";
		QuickDD.toOracleColumn(name,PathKit.getWebRootPath()+"/"+ name + ".docx",QuickDD.findParams(sql));
		renderText("ok");
	}

	
	
	//登录页面
	//@Clear(LoginInterceptor.class)
	@Deprecated
	private void sendLogin(){
		String user = this.getPara("user");
		String pass = this.getPara("pass");
		
		if(user!=null&&pass!=null&&!"".equals(user)&&!"".equals(pass)){
		//	pass = AES.Decrypt(pass);
			Record userbean = Db.findFirst("select * from data_statement_account where account=? and password=? and status=1",user,pass);
			if(userbean!=null){
				//登录成功
				ServletPro.saveCookie(this.getResponse(), "uuu", userbean.getStr("account"));
				this.setSessionAttr("user", userbean);
				this.setAttr("res", 1);
				this.setAttr("msg", "登录成功！");
			}else{
				this.setAttr("res", 0);
				this.setAttr("msg", "用户名或密码错误");
			}
			
		}else{
			this.setAttr("res", 0);
			this.setAttr("msg", "用户名或密码错误");
			
		}
		renderJson();
	}
	
	public void logout(){
		this.setSessionAttr("user", null);
	}
	
	
	
	

	
	/**
	 * 跳转到数据查询页
	 * @author 邢超
	 * 创建时间：2017年11月9日
	 *
	 */
	//@ActionKey("/busList")
	@Deprecated
	private void busList(){		
		Record user = this.getSessionAttr("user");
		List<Record> grouplist = Db.find("select id,classname from data_statement_class where  status=1 and  id in(select classid from data_statement where status=1 and id in(select sid from data_statement_limit where aid = ?)) order by id",user.get("id"));
		this.setAttr("grouplist", grouplist);
		renderJsp("busList.jsp");
	}
	
	@ActionKey("/newbusList")
	public void newbusList(){		
		Record user = this.getSessionAttr("user");
		List<Record> grouplist = Db.find("select id,classname from data_statement_class where status=1 and id in(select classid from data_statement where status=1 and id in(select sid from data_statement_limit where aid = ?)) order by id",user.get("id"));
		this.setAttr("grouplist", grouplist);
		renderJsp("newbusList.jsp");
	}
	

	

	
	/**
	 * 
	 * 根据分组查询sql名称和id
	 * @author 邢超
	 * 创建时间：2017年9月22日
	 *
	 */
	public void findName(){
		Integer classid = this.getParaToInt("classid");
		Record user = this.getSessionAttr("user");
		List<Record> namelist = Db.find("select id,name from data_statement where classid=? and status=1 and id in(select sid from data_statement_limit where aid = ?) order by createtime desc",classid,user.get("id"));
		this.setAttr("namelist", namelist);
		renderJson();
	}
	
	
	
	
	/**
	 * 查询查询的语句和条件
	 */
	public void findSql(){
		Integer id = this.getParaToInt("id");
		Record user = this.getSessionAttr("user");
		Record re = Db.findFirst("select s.*,l.limits from (select * from data_statement_limit where aid=?) l left join data_statement s  on l.sid = s.id  where s.id = ? ",user.get("id"),id);
		if(re!=null){
			String tablename = re.getStr("tablename");
			String wheres = re.getStr("wheres");
			String inserts = re.getStr("inserts");
			String updates = re.getStr("updates");
			String buttonconf = re.getStr("buttonconf");
			
			if(wheres==null||"".equals(wheres)){
				wheres = "{}";//"{\"id\":{\"name\":\"id\",\"explain\":\"ID\",\"type\":\"number\"}}";
			}
			if(inserts==null||"".equals(inserts)){
				inserts = "{}";//"{\"id\":{\"name\":\"id\",\"explain\":\"ID\",\"type\":\"number\"}}";
			}
			if(updates==null||"".equals(updates)){
				updates = "{}";//"{\"id\":{\"name\":\"id\",\"explain\":\"ID\",\"type\":\"number\"}}";
			}
			if(buttonconf==null||"".equals(buttonconf)){
				buttonconf = "{}";//"{\"id\":{\"name\":\"id\",\"explain\":\"ID\",\"type\":\"number\"}}";
			}
			
			if(tablename!=null&&!"".equals(tablename)){
				String sql_oracle = "select * from user_col_comments where table_name=?";
				String sql_mysql = "select table_name,column_name,column_comment comments from information_schema.`COLUMNS` where table_name=?";
				List<Record> comments =  Db.find(driver.indexOf("oracle")>0?sql_oracle:sql_mysql,tablename.toUpperCase());
				this.setAttr("comments", comments);
			}
			re.set("wheres", JSONObject.fromObject(wheres));
			re.set("inserts", JSONObject.fromObject(inserts));
			re.set("updates", JSONObject.fromObject(updates));
			re.set("buttonconf", JSONObject.fromObject(buttonconf));
			
			this.setAttr("sql",re);
		}
	
		renderJson();
	}
	

	
	
	/**
	 * 修改指定报表的查询条件
	 * @author 邢超
	 * 创建时间：2017年11月9日
	 *
	 */
	@Deprecated
	private void updateWheres(){
		String sqlid = this.getPara("sqlid");
		String text = this.getPara("text");
		String sql = "update data_statement set wheres = ? where id = ?";
		
		try {
			JSONObject json = JSONObject.fromObject(text);
			if(Db.update(sql,json.toString(),sqlid)>0){
				this.setAttr("res", 1);
				this.setAttr("msg", "保存成功");				
			}else{
				this.setAttr("res", -2);
				this.setAttr("msg", "保存失败！");
			}
		} catch (Exception e) {
			this.setAttr("res", -1);
			this.setAttr("msg", "数据格式有误！");
		}
		renderJson();
	}
	

	
}
